package com.quantdo.service;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.apache.derby.jdbc.EmbeddedDriver;

import com.quantdo.pojo.ServerDto;

public class ServerManageDao {
	private static final String DRIVER = EmbeddedDriver.class.getName();
//	private static final String URI = "jdbc:derby:tomcatmanage;create=true";
	private static final String URI = "jdbc:derby:D:/tomcatmanage;create=true";

	private static Connection connection;

	static {
		try {
			Class.forName(DRIVER);
			connection = DriverManager.getConnection(URI);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public ServerManageDao() {
		try {
			init();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public static void init() throws SQLException {//always | by default
		String createTabel = "create table tm_tomcatmanage(id int not null generated by default as identity (start with 1, increment by 1) primary key, serveraddr varchar(30), username varchar(30), password varchar(30))";
		String selectExistsTable = "select count(1) from SYS.SYSTABLES t where t.TABLETYPE='T' and t.TABLENAME='TM_TOMCATMANAGE'";
		String selectDeleteTable = "drop table TM_TOMCATMANAGE";
		Statement statement = connection.createStatement();
		ResultSet resultSet = statement.executeQuery(selectExistsTable);
		if (resultSet.next() && 0 == resultSet.getInt(1)) {
			statement.execute(createTabel);
		}else {
			//statement.execute(selectDeleteTable);
			//statement.execute(createTabel);
		}
		statement.close();
	}

	public static void main(String[] args) throws SQLException {
		ServerManageDao serverManageDao = new ServerManageDao();
		List<ServerDto> list = serverManageDao.selectList();
		if(!list.isEmpty()) {
			serverManageDao.getByDto(list.get(0));
			serverManageDao.insert(list.get(0));
			serverManageDao.delete(list.get(0));
		}
	}

	public List<ServerDto> selectList() throws SQLException {
		List<ServerDto> result = new ArrayList<>();
		String selectList = "select * from TM_TOMCATMANAGE OFFSET 0 ROWS     FETCH NEXT 10 ROWS ONLY";
		PreparedStatement preparedStatement = connection.prepareStatement(selectList);
		ResultSet resultSet = preparedStatement.executeQuery();
		ServerDto serverDto;
		while (resultSet.next()) {
			serverDto = new ServerDto();
			serverDto.setId(resultSet.getInt(1));
			serverDto.setServerAddr(resultSet.getString(2));
			serverDto.setUserName(resultSet.getString(3));
			serverDto.setPassword(resultSet.getString(4));
			result.add(serverDto);
		}
		return result;
	}

	public ServerDto getByDto(ServerDto serverDto) throws SQLException {
		ServerDto result = null;
		String getByDto = "select * from TM_TOMCATMANAGE where serveraddr=? and username=?";
		PreparedStatement preparedStatement = connection.prepareStatement(getByDto);
		preparedStatement.setString(1, serverDto.getServerAddr());
		preparedStatement.setString(2, serverDto.getUserName());
		ResultSet resultSet = preparedStatement.executeQuery();
		if (resultSet.next()) {
			result = new ServerDto();
			result.setId(resultSet.getInt(1));
			result.setServerAddr(resultSet.getString(2));
			result.setUserName(resultSet.getString(3));
			result.setPassword(resultSet.getString(4));
		}
		return result;
	}

	public boolean insert(ServerDto serverDto) throws SQLException {
		String insertSql = "insert into TM_TOMCATMANAGE(serveraddr,username,password) values(?, ?, ?)";
		PreparedStatement preparedStatement = connection.prepareStatement(insertSql);
		preparedStatement.setString(1, serverDto.getServerAddr());
		preparedStatement.setString(2, serverDto.getUserName());
		preparedStatement.setString(3, serverDto.getPassword());
		return preparedStatement.execute();
	}

	public boolean delete(ServerDto serverDto) throws SQLException {
		String deleteSql = "delete from TM_TOMCATMANAGE where id=?";
		PreparedStatement preparedStatement = connection.prepareStatement(deleteSql);
		preparedStatement.setInt(1, serverDto.getId());
		return preparedStatement.execute();
	}
}
